package com.aizhixin.test.question.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

import javax.print.attribute.TextSyntax;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import com.aizhixin.test.question.dto.QuestionDTO;
import com.aizhixin.test.question.dto.answerRecordDTO;
import com.aizhixin.test.question.dto.bestKonlege;
import com.aizhixin.test.question.dto.judgeQuestionNeedDTO;
import com.aizhixin.test.question.vo.MostKonwledgeVO;

import aj.org.objectweb.asm.Type;
import net.bytebuddy.asm.Advice.OffsetMapping.Target.AbstractReadOnlyAdapter;

@Component
public class answerRecordDAO {
@Autowired
private JdbcTemplate jdbcTemplate;
/*
 * 查看用户擅长的领域（知识点）
 * SELECT a.USER_ID, a.KNOWLEDGE, COUNT(1) AS total_kowledge FROM answer_record AS a WHERE a.USWER_THIS_STEM_RIGHT_OR_NOT = 1 AND a.USER_ID = 1 GROUP BY	a.KNOWLEDGE ORDER BY	total_kowledge DESC LIMIT 0,3
 * 
 */
public List<bestKonlege> getUserMaxKnowledge(Long userId){
	final String sql="SELECT a.USER_ID, a.KNOWLEDGE, COUNT(1) AS total_kowledge FROM answer_record AS a WHERE a.USWER_THIS_STEM_RIGHT_OR_NOT = 1 AND a.USER_ID = ? GROUP BY	a.KNOWLEDGE ORDER BY total_kowledge DESC LIMIT 0,3";
	List<bestKonlege> list =jdbcTemplate.query(sql, 
			new Object[] {userId},
			new int[] {Type.LONG},
			new RowMapper<bestKonlege>() {
				public bestKonlege mapRow(ResultSet rs,
						int rowNum)
				throws SQLException{
					bestKonlege test =new bestKonlege();
					test.setUserId(userId);
					test.setKnowledfe(rs.getString("KNOWLEDGE"));
					return test ;
				}
			});
	if (null != list && list.size() > 0) {
		return list;
	}else {
		return null;
		}
	}

	public List<MostKonwledgeVO> getUsefiveKnowledge(Long userid) {
		final String sql = "SELECT p.USER_ID,p.KNOWLEDGE,SUM(p.c) as allnumber,SUM(p.r) as rightNumber FROM\r\n"
				+ "(SELECT a.USER_ID,a.KNOWLEDGE, 1 as c,IF(a.USWER_THIS_STEM_RIGHT_OR_NOT=1,1,0)"
				+ " as r FROM answer_record as a WHERE a.USER_ID=?)"
				+ "as p GROUP BY p.USER_ID,p.KNOWLEDGE ORDER BY SUM(p.r) DESC LIMIT 0,5;";
		List<MostKonwledgeVO> list = jdbcTemplate.query(sql,
				new Object[] { userid },
				new int[] { Type.LONG },
				new RowMapper<MostKonwledgeVO>() {
					public MostKonwledgeVO mapRow(ResultSet rs, int rowNum) throws SQLException {
						MostKonwledgeVO test = new MostKonwledgeVO();
						test.setUserId(userid);
						test.setKonwledge(rs.getString("KNOWLEDGE"));
						test.setRightNumber(rs.getInt("rightNumber"));
						test.setAllNumber(rs.getInt("allnumber"));
						return test;
					}
				});
		if (null != list && list.size() > 0) {
			return list;
		} else {
			return null;
		}

	}

}
